ALTER TABLE CHI_TIEU 
ADD
NOTES NTEXT

create PROC SP_INSERT_CHI_TIEU
@MUC_DICH NVARCHAR(200), 
@SO_TIEN FLOAT, 
@NGUOI_CHI INT, 
@NGAY_CHI DATE, 
@SO_THUE_PHONG INT, 
@NOTES NTEXT,
@USR_ID INT
AS
BEGIN
	INSERT INTO CHI_TIEU
	(
		MUC_DICH,
		SO_TIEN,
		NGUOI_CHI,
		NGAY_CHI,
		SO_THUE_PHONG,
		NOTES,
		CREATE_BY,
		CREATE_AT,
		ACTIVE
	)
	VALUES
	(
		@MUC_DICH, 
		@SO_TIEN, 
		@NGUOI_CHI, 
		@NGAY_CHI, 
		CASE @SO_THUE_PHONG 
		WHEN 0 THEN
		NULL
		ELSE @SO_THUE_PHONG END, 
		@NOTES,
		@USR_ID, 
		GETDATE(),
		'A'
	)
END

CREATE PROC SP_UPDATE_CHI_TIEU
@ID INT,
@MUC_DICH NVARCHAR(200), 
@SO_TIEN FLOAT, 
@NGAY_CHI DATE, 
@SO_THUE_PHONG INT, 
@USR_ID INT
AS
BEGIN
	UPDATE CHI_TIEU
	SET MUC_DICH = @MUC_DICH, 
	SO_TIEN = @SO_TIEN, 
	NGAY_CHI = @NGAY_CHI, 
	SO_THUE_PHONG = 
	CASE @SO_THUE_PHONG 
	WHEN 0 THEN 
		 NULL
	ELSE @SO_THUE_PHONG END, 
	UPDATE_AT = GETDATE(), 
	UPDATE_BY = @USR_ID
	WHERE  ID = @ID
END


CREATE PROCEDURE SP_GET_PHONG_WITH_THUE_PHONG
AS
BEGIN
	SELECT 
		THUE_PHONG.ID, 
		PHONG.SO_PHONG
	FROM THUE_PHONG, PHONG
	WHERE THUE_PHONG.ACTIVE = 'A'
	AND THUE_PHONG.MA_PHONG = PHONG.ID
	AND THUE_PHONG.NGAY_TRA IS NULL
END

create PROC SP_GET_CHI_TIEU
@NGUOI_CHI INT, 
@NGAY_CHI_FROM DATE, 
@NGAY_CHI_TO DATE,
@SO_THUE_PHONG INT, 
@TINH_TRANG INT 
AS
BEGIN
	SELECT 
		CHI_TIEU.ID, 
		MUC_DICH, 
		ISNULL(SO_TIEN,0) SO_TIEN, 
		NGUOI_CHI, 
		NGAY_CHI,
		USR.FULL_NAME, 
		PHONG.SO_PHONG, 
		KHACH_THUE.HO_VA_TEN
	FROM 
		CHI_TIEU
		LEFT JOIN THUE_PHONG ON CHI_TIEU.SO_THUE_PHONG = THUE_PHONG.ID
		LEFT JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN KHACH_THUE ON KHACH_THUE.SO_THUE_PHONG = THUE_PHONG.ID AND KHACH_THUE.LEVEL = 1
		JOIN USR ON CHI_TIEU.NGUOI_CHI = USR.ID
	WHERE 
		    (@NGUOI_CHI = 0 OR CHI_TIEU.NGUOI_CHI = @NGUOI_CHI)
		AND (@NGAY_CHI_FROM IS NULL OR CAST(CHI_TIEU.NGAY_CHI AS DATE) >= CAST(@NGAY_CHI_FROM AS DATE))
		AND (@NGAY_CHI_TO IS NULL OR CAST(CHI_TIEU.NGAY_CHI AS DATE) <= CAST(@NGAY_CHI_TO AS DATE))
		AND (@SO_THUE_PHONG = -1 OR (@SO_THUE_PHONG = 0 AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL) OR (CHI_TIEU.SO_THUE_PHONG = @SO_THUE_PHONG))
		AND ((@TINH_TRANG = 0 AND CHI_TIEU.ACTIVE IN ('A','C'))
				OR (@TINH_TRANG = 1 AND CHI_TIEU.ACTIVE = 'A' AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL) 
				OR (@TINH_TRANG = 2 AND CHI_TIEU.ACTIVE = 'C' AND CHI_TIEU.SO_THUE_PHONG IS NOT NULL))
	ORDER BY NGAY_CHI DESC
END


alter PROC SP_DELETE_THU_TIEN
@ID INT, 
@USR_ID INT
AS
BEGIN
	DECLARE @THUE_PHONG_ID INT;
	DECLARE @MAX_ID INT;
	SET @THUE_PHONG_ID = (SELECT TOP 1SO_THUE_PHONG FROM THU_TIEN WHERE ACTIVE = 'A' AND ID = @ID)
	IF @THUE_PHONG_ID IS NOT NULL
	BEGIN
		 SET @MAX_ID = (SELECT MAX(ID) FROM THU_TIEN WHERE SO_THUE_PHONG = @THUE_PHONG_ID AND ACTIVE = 'A')
		 IF @MAX_ID = @ID
		 BEGIN
			UPDATE THUE_PHONG 
			SET NGAY_TRA = NULL,
			UPDATE_AT = GETDATE(), 
			UPDATE_BY = @USR_ID
			WHERE ID = @THUE_PHONG_ID  
		 END		 
		 UPDATE THU_TIEN 
		 SET ACTIVE = 'I',
		 UPDATE_AT = GETDATE(), 
		 UPDATE_BY = @USR_ID
		 WHERE ID = @ID;
	END
	ELSE
	BEGIN
		UPDATE THU_TIEN 
		SET ACTIVE = 'I',
		UPDATE_AT = GETDATE(), 
		UPDATE_BY = @USR_ID
		WHERE ID = @ID
	END
END

exec SP_DELETE_THU_TIEN 23,1


ALTER PROC [dbo].[SP_TRA_PHONG]
@SO_THUE_PHONG INT, 
@NGAY_TRA DATE,
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT, 
@DON_GIA_NUOC FLOAT, 
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@CHI_PHI_KHAC FLOAT ,
@USR_ID INT
AS
BEGIN
	UPDATE THUE_PHONG
	SET 
	NGAY_TRA = @NGAY_TRA, 
	UPDATE_AT = GETDATE(), 
	UPDATE_BY = @USR_ID
	WHERE ID = @SO_THUE_PHONG;
	
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG, 
		NGAY_THU, 
		NGUOI_THU,
		CHI_SO_NUOC_TRUOC, 
		CHI_SO_NUOC_SAU, 
		DON_GIA_NUOC, 
		CHI_SO_DIEN_TRUOC, 
		CHI_SO_DIEN_SAU, 
		DON_GIA_DIEN, 
		TIEN_PHU_THU, 
		CREATE_BY, 
		CREATE_AT, 
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG, 
		@NGAY_TRA, 
		@USR_ID,
		@CHI_SO_NUOC_TRUOC,
		@CHI_SO_NUOC_SAU,
		@DON_GIA_NUOC,
		@CHI_SO_DIEN_TRUOC,
		@CHI_SO_DIEN_SAU,
		@DON_GIA_DIEN,
		@CHI_PHI_KHAC, 
		@USR_ID, 
		GETDATE(), 
		'A'
	)
	
	UPDATE CHI_TIEU
	SET 
	ACTIVE = 'C', -- COMPLETE,
	MA_PHIEU_THU =  (SELECT SCOPE_IDENTITY())
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG;
END



CREATE FUNCTION F_GET_MAX_ID_OF_THU_TIEN
(@SO_THUE_PHONG INT )
RETURNS INT
AS
BEGIN
 RETURN (SELECT MAX(ID) FROM THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG)
END

ALTER PROC [dbo].[SP_GET_DANH_SACH_PHIEU_THU]
@SEARCH_STRING NVARCHAR(MAX), 
@FROM_DATE DATE, 
@TO_DATE DATE
AS
BEGIN
	SELECT 
		THUE_PHONG.ID SO_THUE_PHONG, 
		THUE_PHONG.MA_PHONG, 
		KHACH_THUE.HO_VA_TEN,
		KHACH_THUE.HINH_ANH,
		PHONG.SO_PHONG,
		ISNULL(THU_TIEN.NGAY_THU, GETDATE()) NGAY_DONG, 
		(ISNULL(THU_TIEN.CHI_SO_NUOC_SAU,0) - ISNULL(THU_TIEN.CHI_SO_NUOC_TRUOC,0))*ISNULL(THU_TIEN.DON_GIA_NUOC,0) TIEN_NUOC,
		(ISNULL(THU_TIEN.CHI_SO_DIEN_SAU,0) - ISNULL(THU_TIEN.CHI_SO_DIEN_TRUOC,0))*ISNULL(THU_TIEN.DON_GIA_DIEN,0) TIEN_DIEN,
		ISNULL(THU_TIEN.TIEN_PHONG,0) TIEN_PHONG,
		(ISNULL(THU_TIEN.TIEN_GUI_XE,0) + ISNULL(THU_TIEN.TIEN_PHU_THU,0)) CHI_PHI_KHAC,
		THU_TIEN.ID THU_TIEN_ID, 
		USR.FULL_NAME, 
		DBO.F_GET_MAX_ID_OF_THU_TIEN(THUE_PHONG.ID) MAX_ID
	FROM 
		THUE_PHONG
		JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN THU_TIEN ON THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG
		JOIN KHACH_THUE ON THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG		
		JOIN USR ON THU_TIEN.NGUOI_THU = USR.ID
	WHERE
		THUE_PHONG.ACTIVE = 'A'
		AND KHACH_THUE.LEVEL = 1
		AND (@FROM_DATE IS NULL OR CAST(THU_TIEN.NGAY_THU AS DATE) >= CAST(@FROM_DATE AS DATE))
		AND (@TO_DATE IS NULL OR CAST(THU_TIEN.NGAY_THU AS DATE) <= CAST(@TO_DATE AS DATE))
		AND (@SEARCH_STRING = '' OR @SEARCH_STRING IS NULL OR UPPER(KHACH_THUE.HO_VA_TEN) LIKE '%' + UPPER(@SEARCH_STRING) + '%' OR  CAST(PHONG.SO_PHONG AS NVARCHAR) = @SEARCH_STRING )
		AND KHACH_THUE.ACTIVE = 'A'
		AND THU_TIEN.ACTIVE = 'A'
END


